import sqlite3
import hashlib
import time
from datetime import datetime, timedelta
from typing import Optional, Tuple
import os
import logging
from pathlib import Path

# 配置日志
logging.basicConfig(level=logging.INFO)
logger = logging.getLogger(__name__)

class Database:
    def __init__(self, db_file="sensemore.db"):
        # 获取项目根目录
        project_root = Path(__file__).parent.parent.parent.parent.parent
        
        # 创建数据目录
        data_dir = project_root / "data"
        data_dir.mkdir(exist_ok=True)
        
        # 设置数据库文件路径
        self.db_file = str(data_dir / db_file)
        logger.info(f"Database file path: {self.db_file}")
        logger.info(f"Current working directory: {os.getcwd()}")
        
        self._init_db()

    def _init_db(self):
        with sqlite3.connect(self.db_file) as conn:
            cursor = conn.cursor()
            # 用户表：添加时间戳字段
            cursor.execute('''
                CREATE TABLE IF NOT EXISTS users (
                    id INTEGER PRIMARY KEY AUTOINCREMENT,
                    username TEXT UNIQUE NOT NULL,
                    email TEXT UNIQUE NOT NULL,
                    password TEXT NOT NULL,
                    create_time INTEGER NOT NULL,
                    update_time INTEGER NOT NULL
                )
            ''')
            
            # 登录记录表
            cursor.execute('''
                CREATE TABLE IF NOT EXISTS user_login (
                    id INTEGER PRIMARY KEY AUTOINCREMENT,
                    user_id INTEGER NOT NULL,
                    token TEXT NOT NULL,
                    login_time INTEGER NOT NULL,
                    expire_time INTEGER NOT NULL,
                    FOREIGN KEY (user_id) REFERENCES users (id)
                )
            ''')
            conn.commit()

    def _hash_password(self, password: str) -> str:
        return hashlib.md5(password.encode()).hexdigest()

    def _get_timestamp(self) -> int:
        return int(time.time())

    def create_user(self, username: str, email: str, password: str) -> tuple[int, str]:
        hashed_password = self._hash_password(password)
        current_time = self._get_timestamp()
        
        try:
            with sqlite3.connect(self.db_file) as conn:
                cursor = conn.cursor()
                cursor.execute(
                    """INSERT INTO users 
                       (username, email, password, create_time, update_time) 
                       VALUES (?, ?, ?, ?, ?)""",
                    (username, email, hashed_password, current_time, current_time)
                )
                conn.commit()
                return cursor.lastrowid, "User registered successfully"
        except sqlite3.IntegrityError as e:
            if "username" in str(e):
                raise ValueError("Username already exists")
            elif "email" in str(e):
                raise ValueError("Email already exists")
            raise ValueError("Registration failed")

    def verify_user(self, username: str, password: str) -> Optional[Tuple[int, str, str]]:
        hashed_password = self._hash_password(password)
        current_time = self._get_timestamp()
        # Token 有效期设为24小时
        token_expire_time = current_time + (24 * 60 * 60)
        
        with sqlite3.connect(self.db_file) as conn:
            cursor = conn.cursor()
            # 验证用户
            cursor.execute(
                "SELECT id, email FROM users WHERE username = ? AND password = ?",
                (username, hashed_password)
            )
            result = cursor.fetchone()
            
            if not result:
                return None
                
            user_id, email = result
            
            # 生成新token
            token = hashlib.sha256(f"{username}{current_time}".encode()).hexdigest()
            
            # 记录登录信息
            cursor.execute(
                """INSERT INTO user_login 
                   (user_id, token, login_time, expire_time) 
                   VALUES (?, ?, ?, ?)""",
                (user_id, token, current_time, token_expire_time)
            )
            
            # 更新用户的更新时间
            cursor.execute(
                "UPDATE users SET update_time = ? WHERE id = ?",
                (current_time, user_id)
            )
            
            conn.commit()
            return user_id, email, token

    def verify_token(self, token: str) -> Optional[int]:
        current_time = self._get_timestamp()
        
        with sqlite3.connect(self.db_file) as conn:
            cursor = conn.cursor()
            cursor.execute(
                """SELECT user_id FROM user_login 
                   WHERE token = ? AND expire_time > ?
                   ORDER BY login_time DESC LIMIT 1""",
                (token, current_time)
            )
            result = cursor.fetchone()
            return result[0] if result else None

    def update_user(self, user_id: int, **kwargs) -> bool:
        if not kwargs:
            return False
            
        current_time = self._get_timestamp()
        
        # 构建UPDATE语句
        set_values = []
        params = []
        for key, value in kwargs.items():
            if key in ['email', 'password']:
                set_values.append(f"{key} = ?")
                params.append(value if key != 'password' else self._hash_password(value))
        
        if not set_values:
            return False
            
        # 添加更新时间
        set_values.append("update_time = ?")
        params.append(current_time)
        params.append(user_id)
        
        query = f"UPDATE users SET {', '.join(set_values)} WHERE id = ?"
        
        try:
            with sqlite3.connect(self.db_file) as conn:
                cursor = conn.cursor()
                cursor.execute(query, params)
                conn.commit()
                return cursor.rowcount > 0
        except sqlite3.IntegrityError:
            return False
